"""
外汇数据查询脚本
查询并显示数据库中的外汇数据
"""
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from forex_spider.pipelines import ForexTable

def query_forex_data():
    """查询外汇数据"""
    try:
        # 连接SQLite数据库
        engine = create_engine('sqlite:///forex_data.db')
        Session = sessionmaker(bind=engine)
        session = Session()
        
        # 查询所有外汇数据
        forex_records = session.query(ForexTable).order_by(ForexTable.currency).all()
        
        if not forex_records:
            print("❌ 数据库中暂无外汇数据")
            return
        
        # 按照作业要求的格式输出
        print("\n" + "=" * 120)
        print(f"{'货币名称':<15} {'现汇买入价(TBP)':<12} {'现钞买入价(CBP)':<12} {'现汇卖出价(TSP)':<12} {'现钞卖出价(CSP)':<12} {'发布时间(Time)':<20}")
        print("=" * 120)
        
        for record in forex_records:
            print(f"{record.currency:<15} {record.tbp:<12.4f} {record.cbp:<12.4f} "
                  f"{record.tsp:<12.4f} {record.csp:<12.4f} {record.publish_time:<20}")
        
        print(f"\n📈 总共查询到 {len(forex_records)} 条外汇记录")
        
        # 显示统计信息
        print("\n📊 数据统计:")
        currencies = session.query(ForexTable.currency).distinct().count()
        print(f"   货币种类: {currencies} 种")
        
        # 显示最新爬取时间
        latest = session.query(ForexTable).order_by(ForexTable.crawl_time.desc()).first()
        if latest:
            print(f"   最新数据时间: {latest.crawl_time}")
            
    except Exception as e:
        print(f"❌ 查询数据时出错: {e}")
        print("💡 请确保已运行爬虫并生成了数据")
    finally:
        session.close()

def export_to_html():
    """导出为HTML表格（符合作业要求格式）"""
    try:
        engine = create_engine('sqlite:///forex_data.db')
        Session = sessionmaker(bind=engine)
        session = Session()
        
        records = session.query(ForexTable).all()
        
        if not records:
            print("❌ 无数据可导出")
            return
            
        # 生成HTML表格
        html_content = """
        <!DOCTYPE html>
        <html>
        <head>
            <meta charset="UTF-8">
            <title>外汇牌价数据</title>
            <style>
                table { border-collapse: collapse; width: 100%; margin: 20px 0; }
                th, td { border: 1px solid #ddd; padding: 8px; text-align: center; }
                th { background-color: #f2f2f2; }
                tr:nth-child(even) { background-color: #f9f9f9; }
            </style>
        </head>
        <body>
            <h2>招商银行外汇牌价</h2>
            <table>
                <tr>
                    <th>Currency</th>
                    <th>TBP</th>
                    <th>CBP</th>
                    <th>TSP</th>
                    <th>CSP</th>
                    <th>Time</th>
                </tr>
        """
        
        for record in records:
            html_content += f"""
                <tr>
                    <td>{record.currency}</td>
                    <td>{record.tbp:.4f}</td>
                    <td>{record.cbp:.4f}</td>
                    <td>{record.tsp:.4f}</td>
                    <td>{record.csp:.4f}</td>
                    <td>{record.publish_time}</td>
                </tr>
            """
        
        html_content += """
            </table>
            <p>数据更新时间: """ + records[0].crawl_time + """</p>
        </body>
        </html>
        """
        
        # 保存HTML文件
        with open('forex_data.html', 'w', encoding='utf-8') as f:
            f.write(html_content)
            
        print("✅ HTML文件已生成: forex_data.html")
        
    except Exception as e:
        print(f"❌ 导出HTML时出错: {e}")

if __name__ == '__main__':
    print("🔍 外汇数据查询工具")
    print("1. 显示数据")
    print("2. 导出HTML")
    
    choice = input("请选择操作 (1/2): ").strip()
    
    if choice == '1':
        query_forex_data()
    elif choice == '2':
        export_to_html()
    else:
        print("❌ 无效选择")